package yiguan;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.Properties;
import java.util.Iterator;
import java.util.Map;
import java.util.TreeMap;
import java.io.PrintWriter;
import java.io.IOException;
import javax.servlet.http.HttpServletResponse;
import org.json.JSONObject;
import org.json.JSONArray;
import org.json.JSONException;
import org.apache.log4j.Logger;

public class Customer {
	private String cid_;
	private String cname_;
	private String csex_;
	private String ctype_;
	private String idcard_;
	private String cmobile_;
	private String ctel_;
	private String caddress_;
	private String district_;
	private String sid_;
	private String commuid_;
	private String nationality_;
	private String family_;
	private String health_;
	private String residence_;
	private String remark_;
	private int cursum_;
	private int lastpay_;
	private int subsidy_;

	private static Logger logger = Logger.getLogger("Customer");

	public Customer(String cid) {
		cid_ = cid;
	}

	//account pay, use the input connection, in a transaction
	public void pay(Connection conn, int payment) throws SQLException {
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement("update customer set cursum = cursum - ?,lastpay=? where cid=?");
			ps.setInt(1, payment);
			ps.setInt(2, payment);
			ps.setString(3, cid_);
			ps.executeUpdate();
		} catch (SQLException ex) {
			throw ex;
		}
		finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
		}
	}

	//account withdraw, use the input connection, in a transaction
	public void withdraw(Connection conn, int consume) throws SQLException {
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement("update customer set cursum = cursum + ?,lastpay=0 where cid=?");
			ps.setInt(1, consume);
			ps.setString(2, cid_);
			ps.executeUpdate();
		} catch (SQLException ex) {
			throw ex;
		}
		finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
		}
	}

	public static int addServNum(Connection conn, String cid) throws SQLException {
		Statement stmt = null;
		int rownum = 0;

		try {
			stmt = conn.createStatement();
			rownum = stmt.executeUpdate("update customer set servnum = servnum + 1 where cid='" + cid + "'");
		} catch (SQLException ex) {
			throw ex;
		}
		finally {
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
		}

		return rownum;
	}

	//alloc money for district
	public static int alloc() {
		int retval = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		String month = DBUtil.MONTH_FORMAT.format(new Date());

		if (DBUtil.isAllocing()) {
  			return 1000;
  		}

		JSONArray sa = getCustomerStatInfo();
		try {
			conn = DBUtil.openConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement("insert into allocinfo" +
				"(month,district,sanwunum,sanwumoney,dibaonum,dibaomoney,gaolingnum,gaolingmoney,totalnum,totalmoney,state)" +
				" values(?,?,?,?,?,?,?,?,?,?,?)");
			for (int i = 0; i < 7; i++) {
				JSONObject so = (JSONObject)sa.get(i);
				String[] ss = (String[])so.get("cell");
				ps.setString(1, month);
				ps.setString(2, ss[0]);
				ps.setString(3, ss[1]);
				ps.setString(4, ss[2]);
				ps.setString(5, ss[3]);
				ps.setString(6, ss[4]);
				ps.setString(7, ss[5]);
				ps.setString(8, ss[6]);
				ps.setString(9, ss[7]);
				ps.setString(10, ss[8]);
				if (ss[0].equals("0"))
					ps.setString(11, "1");
				else
					ps.setString(11, "0");
				ps.executeUpdate();
			}
			//ps = conn.createStatement();
			//ps.executeUpdate("update customer set cursum = cursum + subsidy");
			conn.commit();
			logger.info("All district customer alloced!");
		} catch(SQLException e) {
			try {
				conn.rollback();
			} catch(SQLException e2) {}
			retval = e.getErrorCode();
			logger.error(e.getErrorCode() + " : " + e.getMessage(), e);
		} catch(JSONException e1) {
			try {
				conn.rollback();
			} catch(SQLException e2) {}
			logger.error(e1.getMessage(), e1);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	// add new column "allocnum" and "isquit" in customer table ---- 20120215
	public static int allcoDistrict(String district) {
		int retval = 0;
		Connection conn = null;
		Statement stmt = null;
		String month = DBUtil.MONTH_FORMAT.format(new Date());
		if (java.util.Arrays.binarySearch(DBUtil.ALLOC_MONTH, month.substring(4)) < 0) {
			return 1001;
		}
		JSONArray sa = getCustomerStatInfo();
		

		try {
			JSONObject so = sa.getJSONObject(Integer.parseInt(district) - 1);
			String[] ss = (String[])so.get("cell");
			conn = DBUtil.openConnection();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			//int r = stmt.executeUpdate("update allocinfo set state='1' where month='" +
			//	month + "' and district='" + district + "'");
			
			int r = stmt.executeUpdate("insert into allocinfo (month,district,sanwunum,sanwumoney,dibaonum,dibaomoney,"
				+ "gaolingnum,gaolingmoney,youfunum,youfumoney,laomonum,laomomoney,totalnum,totalmoney,state) values('"
				+ month + "','" + district + "'," + ss[1] + "," + ss[2] + "," + ss[3] + "," + ss[4]
				+ "," + ss[5] + "," + ss[6] + "," + ss[7] + "," + ss[8] + "," + ss[9] + "," + ss[10]
				+ "," + ss[11] + "," + ss[12] + ",'0')");
			if (r != 1) {
				conn.rollback();
				return retval;
			}
			
			JSONArray allocDetail = getDistrictCustomerStat(district);
			PreparedStatement ps = conn.prepareStatement("insert into allocdetail(month,district,sid,sanwunum,sanwumoney,"
				+ "dibaonum,dibaomoney,gaolingnum,gaolingmoney,youfunum,youfumoney,laomonum,laomomoney,totalnum,totalmoney)"
				+ " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			for (int i = 0; i < allocDetail.length() - 1;i++) {
				JSONObject item = allocDetail.getJSONObject(i);
				ss = (String[])item.get("cell");
				ps.setString(1, month);
				ps.setString(2, district);
				ps.setString(3, ss[13]);
				ps.setString(4, ss[1]);
				ps.setString(5, ss[2]);
				ps.setString(6, ss[3]);
				ps.setString(7, ss[4]);
				ps.setString(8, ss[5]);
				ps.setString(9, ss[6]);
				ps.setString(10, ss[7]);
				ps.setString(11, ss[8]);
				ps.setString(12, ss[9]);
				ps.setString(13, ss[10]);
				ps.setString(14, ss[11]);
				ps.setString(15, ss[12]);
				ps.addBatch();
			}
			ps.executeBatch();
			r = stmt.executeUpdate("update customer set cursum = cursum + subsidy * 3, allocnum=allocnum + 1 where district='"
					+ district + "' and isquit='0'");
			conn.commit();
			logger.info("A district customer alloced: " + district);
		} catch(SQLException e) {
			try {
				conn.rollback();
			} catch(SQLException e2) {}
			retval = e.getErrorCode();
			logger.error(e.getErrorCode() + " : " + e.getMessage(), e);
		} catch (JSONException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}
	
	public static int zeroAccount(String district) {
		String month = DBUtil.MONTH_FORMAT.format(new Date());
		if (java.util.Arrays.binarySearch(DBUtil.ALLOC_MONTH, month.substring(4)) < 0) {
			return 1001;
		}
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select sum(cursum) from customer where district='" + district + "'");
			rs.next();
			int allMoney = rs.getInt(1);
			rs.close();
			stmt.executeUpdate("update customer set cursum=0 where district='" + district + "'");
			logger.info("A disctrict customer reset : " + district + "; all money is " + allMoney);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
		
		return 0;
	}

	public static JSONObject getAllCustomer(Hashtable params, int page, int rows) {
		JSONArray customers = null;
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from customer";
		String sqlCount = "select count(*),sum(cursum),sum(servnum) from customer";
		int total, sum_cursum, sum_servnum;
		int offset = page * rows - rows;
		JSONObject retval = new JSONObject();
		JSONObject userData = new JSONObject();
		String sqlWhere = DBUtil.buildSqlWhere(params);

		if (sqlWhere.length() != 0) {
			sql += sqlWhere;
			sqlCount += sqlWhere;
		}

		try {
			conn = DBUtil.openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sqlCount);
			rs.next();
			total = rs.getInt(1);
			sum_cursum = rs.getInt(2);
			sum_servnum = rs.getInt(3);
			userData.put("cursum", "" + sum_cursum);
			userData.put("servnum", "" + sum_servnum);
			ps = conn.prepareStatement(sql + " order by cid offset ? rows fetch next ? rows only");
			ps.setInt(1, offset);
			ps.setInt(2, rows);
			rs = ps.executeQuery();
			customers = getCustomerFromRS(rs);

			retval.put("page", page);
			retval.put("total",(total / rows) + 1);
			retval.put("records", total);
			retval.put("rows", customers);
			retval.put("userdata", userData);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(JSONException e1) {
			logger.error(e1);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	private static JSONArray getCustomerFromRS(ResultSet rs) {
		JSONArray customers = new JSONArray();

		try {
			while(rs.next()) {
				JSONObject customer = new JSONObject();
				customer.put("id", rs.getString("cid"));
				String[] ss = new String[20];
				ss[0] = rs.getString("cid").trim();
				ss[1] = rs.getString("cname").trim();
				ss[2] = rs.getString("csex");
				ss[3] = rs.getString("nationality").trim();
				ss[4] = rs.getString("ctype");
				ss[5] = rs.getString("idcard").trim();
				ss[6] = rs.getString("family");
				ss[7] = rs.getString("caddress").trim();
				ss[8] = rs.getString("residence").trim();
				ss[9] = rs.getString("district");
				ss[10] = rs.getString("sid");
				ss[11] = rs.getString("commuid");
				ss[12] = rs.getString("health").trim();
				ss[13] = rs.getString("cursum");
				ss[14] = rs.getString("subsidy");
				ss[15] = rs.getString("servnum");
				ss[16] = rs.getString("cmobile").trim();
				ss[17] = rs.getString("ctel").trim();
				ss[18] = rs.getString("shiftdate");
				ss[19] = rs.getString("remark") == null? "":rs.getString("remark");
				customer.put("cell", ss);
				customers.put(customer);
			}
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(JSONException e) {
			logger.error(e.getMessage(), e);
		}

		return customers;
	}

	public static void toExcel(Hashtable params, HttpServletResponse response) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		PrintWriter out;
		StringBuffer sb = new StringBuffer();
		Properties bundle = new Properties();
		String queryStr = DBUtil.buildSqlWhere(params);
		Properties streets = DBUtil.getAllStreet();
		Properties communities = DBUtil.getAllCommunity();
			
		response.reset();
		response.setHeader("Content-Disposition", "attachment; filename=" + System.currentTimeMillis() + ".csv");
		response.setContentType("application/vnd.ms-excel; charset=GBK");
		try {
			out = response.getWriter();
			bundle.load(Customer.class.getResourceAsStream("Customer.properties"));
			for (int i = 1; i <= 17; i++) {
				sb.append(bundle.getProperty("col" + i));
				if (i < 17)
					sb.append(",");
			}
			out.println(sb.toString());
			conn = DBUtil.openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from customer" + queryStr + " order by cid");
			while(rs.next()) {
				sb = new StringBuffer();
				sb.append("\t");
				sb.append(rs.getString("cid"));
				sb.append(",");
				sb.append(rs.getString("cname").trim());
				sb.append(",");
				sb.append(bundle.getProperty(rs.getString("csex")));
				sb.append(",");
				sb.append(rs.getString("nationality").trim());
				sb.append(",");
				sb.append(bundle.getProperty("ctype" + rs.getString("ctype")));
				sb.append(",");
				sb.append("\t");
				sb.append(rs.getString("idcard").trim());
				sb.append(",");
				sb.append(rs.getString("family"));
				sb.append(",");
				sb.append(rs.getString("caddress").trim());
				sb.append(",");
				sb.append(rs.getString("residence").trim());
				sb.append(",");
				sb.append(bundle.getProperty("district" + rs.getString("district")));
				sb.append(",");
				String sid = rs.getString("sid");
				sb.append(sid == null? "":streets.getProperty(sid));
				sb.append(",");
				String commuid = rs.getString("commuid");
				sb.append(commuid == null? "": communities.getProperty(commuid));
				sb.append(",");
				sb.append(rs.getString("health").trim());
				sb.append(",");
				sb.append(rs.getString("cursum"));
				sb.append(",");
				sb.append(rs.getString("subsidy"));
				sb.append(",");
				sb.append("\t");
				sb.append(rs.getString("cmobile").trim());
				sb.append(",");
				sb.append("\t");
				sb.append(rs.getString("ctel").trim());
				out.println(sb.toString());
			}
			out.flush();
			out.close();
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(IOException e2) {
  			logger.error(e2.getMessage(), e2);
  		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

	}

	//stat info about customer by district, ctype
	public static JSONArray getCustomerStatInfo() {
		JSONArray cs = new JSONArray();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int totalMoney = 0, totalNum = 0;
		int total1 = 0, total2 = 0, total3 = 0, total4 = 0, total5 = 0, subsidy1 = 0, subsidy2 = 0, subsidy3 = 0, subsidy4 = 0, subsidy5 = 0;
		int n1, n2, n3, n4, n5, sum1, sum2, sum3, sum4, sum5;

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("select ctype,count(*),sum(subsidy) * 3 from customer where district=? and isquit='0' group by ctype");
			for (int i = 1; i <= 6; i++) {
				String dist = String.valueOf(i);
				JSONObject so = new JSONObject();
				ps.setString(1, dist);
				rs = ps.executeQuery();
				String[] ss = new String[13];
				ss[0] = dist;
				ss[1] = "0";
				ss[2] = "0";
				ss[3] = "0";
				ss[4] = "0";
				ss[5] = "0";
				ss[6] = "0";
				ss[7] = "0";
				ss[8] = "0";
				ss[9] = "0";
				ss[10] = "0";
				n1 = 0;
				n2 = 0;
				n3 = 0;
				n4 = 0;
				n5 = 0;
				sum1 = 0;
				sum2 = 0;
				sum3 = 0;
				sum4 = 0;
				sum5 = 0;
				while (rs.next()) {
					if (rs.getString(1).equals("1")) {
						ss[1] = rs.getString(2);
						n1 = rs.getInt(2);
						total1 += n1;
						ss[2] = rs.getString(3);
						sum1 = rs.getInt(3);
						subsidy1 += sum1;
					} else if (rs.getString(1).equals("2")) {
						ss[3] = rs.getString(2);
						n2 = rs.getInt(2);
						total2 += n2;
						ss[4] = rs.getString(3);
						sum2 = rs.getInt(3);
						subsidy2 += sum2;
					} else if (rs.getString(1).equals("3")) {
						ss[5] = rs.getString(2);
						n3 = rs.getInt(2);
						total3 += n3;
						ss[6] = rs.getString(3);
						sum3 = rs.getInt(3);
						subsidy3 += sum3;
					} else if (rs.getString(1).equals("4")) {
						ss[7] = rs.getString(2);
						n4 = rs.getInt(2);
						total4 += n4;
						ss[8] = rs.getString(3);
						sum4 = rs.getInt(3);
						subsidy4 += sum4;
					} else if (rs.getString(1).equals("5")) {
						ss[9] = rs.getString(2);
						n5 = rs.getInt(2);
						total5 += n5;
						ss[10] = rs.getString(3);
						sum5 = rs.getInt(3);
						subsidy5 += sum5;
					}
				}
				totalNum   += (n1 + n2 + n3 + n4 + n5);
				totalMoney += (sum1 + sum2 + sum3 + sum4 + sum5);
				ss[11] = String.valueOf(n1 + n2 + n3 + n4 + n5);
				ss[12] = String.valueOf(sum1 + sum2 + sum3 + sum4 + sum5);
				so.put("id", dist);
				so.put("cell", ss);
				cs.put(so);
			}
			String[] ss = new String[13];
			ss[0] = "0";
			ss[1] = String.valueOf(total1);
			ss[2] = String.valueOf(subsidy1);
			ss[3] = String.valueOf(total2);
			ss[4] = String.valueOf(subsidy2);
			ss[5] = String.valueOf(total3);
			ss[6] = String.valueOf(subsidy3);
			ss[7] = String.valueOf(total4);
			ss[8] = String.valueOf(subsidy4);
			ss[9] = String.valueOf(total5);
			ss[10] = String.valueOf(subsidy5);
			ss[11] = String.valueOf(totalNum);
			ss[12] = String.valueOf(totalMoney);
			JSONObject so = new JSONObject();
			so.put("id", "0");
			so.put("cell", ss);
			cs.put(so);
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return cs;
	}

	public static void statExcel(HttpServletResponse response) {
		PrintWriter out;
		Properties bundle = new Properties();

		response.reset();
		response.setHeader("Content-Disposition", "attachment; filename=" + System.currentTimeMillis() + ".csv");
		response.setContentType("application/vnd.ms-excel; charset=GBK");
		JSONArray sa = getCustomerStatInfo();

		try {
			out = response.getWriter();
			bundle.load(Customer.class.getResourceAsStream("Customer.properties"));
			for (int i = 1; i <= 13; i++) {
				out.print(bundle.getProperty("statcol" + i));
				if (i != 13)
					out.print(",");
			}
			out.println("");
			for (int i = 0; i <= 6; i++) {
				JSONObject so = (JSONObject)sa.get(i);
				String[] ss = (String[])so.get("cell");
				StringBuffer sb = new StringBuffer();
				sb.append(bundle.getProperty("district" + ss[0]));
				for (int j = 1; j <= 12; j++) {
					sb.append(",");
					sb.append(ss[j]);
				}
				out.println(sb.toString());
			}
			out.flush();
			out.close();
		} catch(Exception e2) {
  			logger.error(e2.getMessage(), e2);
  		}
	}
	
	public static JSONArray getDistrictCustomerStat(String district) {
		JSONArray cs = new JSONArray();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int totalMoney = 0, totalNum = 0;
		int total1 = 0, total2 = 0, total3 = 0, total4 = 0, total5 = 0, subsidy1 = 0, subsidy2 = 0, subsidy3 = 0, subsidy4 = 0, subsidy5 = 0;
		int n1, n2, n3, n4, n5, sum1, sum2, sum3, sum4, sum5;

		try {
			Properties streets = DBUtil.getAllStreetByDistrict(district);
			TreeMap orderedStreets = new TreeMap(streets);
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("select ctype,count(*),sum(subsidy) * 3 from customer where district='"
				+ district + "' and sid=? and isquit='0' group by ctype");
			for (Iterator it = orderedStreets.entrySet().iterator(); it.hasNext();) {
				JSONObject so = new JSONObject();
				Map.Entry entry = (Map.Entry)it.next();
				String sid = (String)entry.getKey();
				ps.setString(1, sid);
				rs = ps.executeQuery();
				String[] ss = new String[14];
				ss[0] = (String)entry.getValue();
				ss[1] = "0";
				ss[2] = "0";
				ss[3] = "0";
				ss[4] = "0";
				ss[5] = "0";
				ss[6] = "0";
				ss[7] = "0";
				ss[8] = "0";
				ss[9] = "0";
				ss[10] = "0";
				n1 = 0;
				n2 = 0;
				n3 = 0;
				n4 = 0;
				n5 = 0;
				sum1 = 0;
				sum2 = 0;
				sum3 = 0;
				sum4 = 0;
				sum5 = 0;
				while (rs.next()) {
					if (rs.getString(1).equals("1")) {
						ss[1] = rs.getString(2);
						n1 = rs.getInt(2);
						total1 += n1;
						ss[2] = rs.getString(3);
						sum1 = rs.getInt(3);
						subsidy1 += sum1;
					} else if (rs.getString(1).equals("2")) {
						ss[3] = rs.getString(2);
						n2 = rs.getInt(2);
						total2 += n2;
						ss[4] = rs.getString(3);
						sum2 = rs.getInt(3);
						subsidy2 += sum2;
					} else if (rs.getString(1).equals("3")) {
						ss[5] = rs.getString(2);
						n3 = rs.getInt(2);
						total3 += n3;
						ss[6] = rs.getString(3);
						sum3 = rs.getInt(3);
						subsidy3 += sum3;
					} else if (rs.getString(1).equals("4")) {
						ss[7] = rs.getString(2);
						n4 = rs.getInt(2);
						total4 += n4;
						ss[8] = rs.getString(3);
						sum4 = rs.getInt(3);
						subsidy4 += sum4;
					} else if (rs.getString(1).equals("5")) {
						ss[9] = rs.getString(2);
						n5 = rs.getInt(2);
						total5 += n5;
						ss[10] = rs.getString(3);
						sum5 = rs.getInt(3);
						subsidy5 += sum5;
					}
				}
				totalNum   += (n1 + n2 + n3 + n4 + n5);
				totalMoney += (sum1 + sum2 + sum3 + sum4 + sum5);
				ss[11] = String.valueOf(n1 + n2 + n3 + n4 + n5);
				ss[12] = String.valueOf(sum1 + sum2 + sum3 + sum4 + sum5);
				ss[13] = sid;
				so.put("id", sid);
				so.put("cell", ss);
				cs.put(so);
			}
			String[] ss = new String[14];
			ss[0] = "";
			ss[1] = String.valueOf(total1);
			ss[2] = String.valueOf(subsidy1);
			ss[3] = String.valueOf(total2);
			ss[4] = String.valueOf(subsidy2);
			ss[5] = String.valueOf(total3);
			ss[6] = String.valueOf(subsidy3);
			ss[7] = String.valueOf(total4);
			ss[8] = String.valueOf(subsidy4);
			ss[9] = String.valueOf(total5);
			ss[10] = String.valueOf(subsidy5);
			ss[11] = String.valueOf(totalNum);
			ss[12] = String.valueOf(totalMoney);
			ss[13] = "";
			JSONObject so = new JSONObject();
			so.put("id", "0");
			so.put("cell", ss);
			cs.put(so);
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return cs;
	}
	
	public static JSONArray getStreetCustomerStat(String district, String month) {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		JSONArray sa = new JSONArray();
		
		try {
			Properties streets = DBUtil.getAllStreetByDistrict(district);
			conn = DBUtil.openConnection();
			st = conn.createStatement();
			String sql = "select sid,sanwunum,sanwumoney,dibaonum,dibaomoney,gaolingnum,gaolingmoney,"
				+ "youfunum,youfumoney,laomonum,laomomoney,totalnum,totalmoney from allocdetail "
				+ "where month='" + month + "' and district='" + district + "' order by sid";
			//logger.info(sql);
			rs = st.executeQuery(sql);
			while (rs.next()) {
				JSONObject so = new JSONObject();
				so.put("id", rs.getString(1));
				String[] ss = new String[13];
				ss[0] = streets.getProperty(rs.getString(1));
				for (int i = 1; i < 13; i++)
					ss[i] = rs.getString(i + 1);
				so.put("cell", ss);
				sa.put(so);
			}
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (st != null) { try { st.close();	} catch (SQLException e) { ; }
				st = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return sa;
	}

	//save new added customer info to DB
	public int saveNewToDB() {
		int retval = -1;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			String sbirth = idcard_.substring(6, 10);
			int cbirth = Integer.parseInt(sbirth);
			conn = DBUtil.openConnection();
			//here shiftdate means added time
			ps = conn.prepareStatement("insert into customer (cid,cname,csex,ctype,cmobile,ctel,caddress,idcard," +
				"subsidy,district,sid,commuid,nationality,family,health,residence,cbirth,shiftdate)" +
				" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,current_date)");
			ps.setString(1, cid_);
			ps.setString(2, cname_);
			ps.setString(3, csex_);
			ps.setString(4, ctype_);
			ps.setString(5, cmobile_);
			ps.setString(6, ctel_);
			ps.setString(7, caddress_);
			ps.setString(8, idcard_);
			ps.setInt(9, subsidy_);
			ps.setString(10, district_);
			ps.setString(11, sid_);
			ps.setString(12, commuid_);
			ps.setString(13, nationality_);
			ps.setString(14, family_);
			ps.setString(15, health_);
			ps.setString(16, residence_);
			ps.setInt(17, cbirth);
			int rownum = ps.executeUpdate();
			if (rownum == 1) {
				retval = 0;
				logger.info("A customer  added : " + cid_ + " " + cname_);
			}
		} catch (SQLException e) {
			retval = e.getErrorCode();
			if (e.getMessage().contains("IDCARD"))
				retval++;
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}

	public int update() {
		int retval = -1;
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("update customer set " +
				"cname=?, csex=?, ctype=?, cmobile=?, ctel=?, caddress=?, idcard=?,subsidy=?," +
				"district=?, sid=?, commuid=?,nationality=?,family=?,health=?,residence=? where cid=? and isquit='0'");
			ps.setString(1, cname_);
			ps.setString(2, csex_);
			ps.setString(3, ctype_);
			ps.setString(4, cmobile_);
			ps.setString(5, ctel_);
			ps.setString(6, caddress_);
			ps.setString(7, idcard_);
			ps.setInt(8, subsidy_);
			ps.setString(9, district_);
			ps.setString(10, sid_);
			ps.setString(11, commuid_);
			ps.setString(12, nationality_);
			ps.setString(13, family_);
			ps.setString(14, health_);
			ps.setString(15, residence_);
			ps.setString(16, cid_);
			int rownum = ps.executeUpdate();
			if (rownum == 1) {
				retval = 0;
				logger.info("A customer  update : " + cid_ + " " + cname_);
			}
		} catch (SQLException e) {
			retval = e.getErrorCode();
			if (e.getMessage().contains("IDCARD"))
				retval++;
			logger.error(e.getMessage(), e);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}
	
	public static int updateRemark(String cid, String remark) {
		Connection conn = null;
		Statement stmt = null;
		int r = 0;
		
		try {
			conn = DBUtil.openConnection();
			stmt = conn.createStatement();
			r = stmt.executeUpdate("update customer set remark='" + remark + "' where cid='" + cid + "'");
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
		
		return r;
	}

	// load a customer info from DB by cid
	public static Customer getCustomerByID(String cid) {
		Customer customer = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("select * from customer where cid=?");
			ps.setString(1, cid);
			rs = ps.executeQuery();
			if (rs.next()) {
				customer = new Customer(cid);
				customer.cid_   = cid;
				customer.cname_ = rs.getString("cname").trim();
				customer.csex_  = rs.getString("csex");
				customer.cmobile_ = rs.getString("cmobile");
				customer.ctel_  = rs.getString("ctel").trim();
				customer.ctype_ = rs.getString("ctype");
				customer.caddress_ = rs.getString("caddress").trim();
				customer.cursum_ = rs.getInt("cursum");
				customer.lastpay_ = rs.getInt("lastpay");
				customer.district_ = rs.getString("district");
				customer.sid_ = rs.getString("sid");
				customer.commuid_ = rs.getString("commuid");
			}
		} catch (Exception e) {
			customer = null;
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return customer;
	}
	
	public static Customer getCustomerByTel(String tel) {
		Customer customer = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.openConnection();
			ps = conn.prepareStatement("select cid,cname,csex,cmobile,ctel,ctype,caddress," + 
				"cursum,lastpay,district,sid,commuid,remark from customer where cmobile=? or ctel=?");
			ps.setString(1, tel);
			ps.setString(2, tel);
			rs = ps.executeQuery();
			if (rs.next()) {
				customer = new Customer("");
				customer.cid_   = rs.getString("cid");
				customer.cname_ = rs.getString("cname").trim();
				customer.csex_  = rs.getString("csex");
				customer.cmobile_ = rs.getString("cmobile");
				customer.ctel_  = rs.getString("ctel").trim();
				customer.ctype_ = rs.getString("ctype");
				customer.caddress_ = rs.getString("caddress").trim();
				customer.cursum_ = rs.getInt("cursum");
				customer.lastpay_ = rs.getInt("lastpay");
				customer.district_ = rs.getString("district");
				customer.sid_ = rs.getString("sid");
				customer.commuid_ = rs.getString("commuid");
				customer.remark_ = rs.getString("remark") == null? "" : rs.getString("remark");
			}
		} catch (Exception e) {
			customer = null;
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return customer;
	}

	public JSONObject toJSON() {
		JSONObject so = new JSONObject();
		try {
			so.put("cid", cid_);
			so.put("cname", cname_);
			so.put("csex", csex_);
			so.put("cmobile", cmobile_);
			so.put("ctel", ctel_);
			so.put("ctype",ctype_);
			so.put("caddress", caddress_);
			so.put("district", district_);
			so.put("sid", sid_);
			so.put("commuid", commuid_);
			so.put("account", String.valueOf(cursum_));
		} catch(JSONException e) {}

		return so;
	}

	public static boolean quitCustomerByID(String cid) {
		boolean retval = false;
		Connection conn = null;
		Statement stmt = null;

		try {
			conn = DBUtil.openConnection();
			stmt = conn.createStatement();
			int rownum = stmt.executeUpdate("update customer set isquit='1',shiftdate=current_date where cid='" + cid + "'");
			logger.info("A customer quited : " + cid);
			if (rownum == 1)
				retval = true;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return retval;
	}
	
	public static int restoreCustomerByID(String cid) {
		int rownum = 0;
		Connection conn = null;
		Statement stmt = null;

		try {
			conn = DBUtil.openConnection();
			stmt = conn.createStatement();
			rownum = stmt.executeUpdate("update customer set isquit='0' where cid='" + cid + "'");
			logger.info("A customer restored : " + cid);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return rownum;
	}
	
	public static int deleteQuitCustomer(String cid) {
		Connection conn = null;
		Statement stmt = null;
		int rownum = 0;

		try {
			conn = DBUtil.openConnection();
			stmt = conn.createStatement();
			rownum = stmt.executeUpdate("delete from customer where isquit='1' and cid='" + cid + "'");
			logger.info("A quited customer deleted : " + cid);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}

		return rownum;
	}

	public String getCid() {
		return cid_;
	}

	public String getDistrict() {
		return district_;
	}

	public String getSid() {
		return sid_;
	}

	public String getCommuid() {
		return commuid_;
	}

	public int getCursum() {
		return cursum_;
	}

	public int getLastpay() {
		return lastpay_;
	}

	public void set(String cname, String csex, String ctype, String idcard, String cmobile, String ctel,
			String caddress, int subsidy, String district, String sid, String commuid,
			String nationality, String family, String health, String residence) {
		cname_ = cname;
		csex_ = csex;
		ctype_ = ctype;
		idcard_ = idcard;
		cmobile_ = cmobile;
		ctel_ = ctel;
		caddress_ = caddress;
		subsidy_ = subsidy;
		district_ = district;
		sid_ = sid;
		commuid_ = commuid;
		nationality_ = nationality;
		family_ = family;
		health_ = health;
		residence_ = residence;
	}
}